﻿using System;
using System.Threading;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Diagnostics;
using System.IO;
using System.Xml;

namespace OneEightySquaredDbScripter
{
    class DbScriptingEngine
    {
        #region Fields

        // to let the UI know what is going on
        public delegate void ObjectScriptedDelegate();
        public event ObjectScriptedDelegate ObjectScripted;

        // provides for main thread
        private Server _server; 
        private Scripter _scripterCreate;
        private Scripter _scripterDrop;
        private Database _database;

        // provides for scripting threads
        private List<string> _tables;
        private List<string> _sprocs;
        private List<string> _views;
        private ScriptingLayout _scriptingLayout;
       
        #endregion

        #region C'tor

        public DbScriptingEngine(string serverName, string dbName, ScriptingLayout layout)
        {
            _server = new Server(serverName);
            if (!_server.Databases.Contains(dbName))
            {
                throw new Exception(string.Format("Database '{0}' does not exist on '{1}'.", dbName, serverName));
            }

            _scriptingLayout = layout;

            _server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
            _server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
            _server.SetDefaultInitFields(typeof(View), "IsSystemObject");

            _database = _server.Databases[dbName];
            _scripterCreate = BuildCreateScripter(_server);
            _scripterDrop = BuildDropScripter(_server);
        }

        #endregion

        #region Properties

        public ScriptingOptions CreateScripterOptions
        {
            get { return _scripterCreate.Options; }
        }

        public ScriptingOptions DropScripterOptions
        {
            get { return _scripterDrop.Options; }
        }

        public StoredProcedureCollection StoredProcedures
        {
            get { return _database.StoredProcedures; }
        }

        public TableCollection Tables
        {
            get { return _database.Tables; }
        }

        public ViewCollection Views
        {
            get { return _database.Views; }
        }     

        #endregion

        protected virtual void OnObjectScripted()
        {
            if (ObjectScripted != null)
            {
                ObjectScripted();
            }
        }

        public void GenerateSprocScripts(List<string> sprocs)
        {
            if (0 == sprocs.Count) return;
            _sprocs = sprocs;
           
            Thread thread = new Thread(GenerateSprocScriptsProc);
            thread.Start();
        }

        public void GenerateTableScripts(List<string> tables)
        {
            if (0 == tables.Count) return;
            _tables = tables;
            
            Thread thread = new Thread(GenerateTableScriptsProc);
            thread.Start();
        }

        public void GenerateViewScripts(List<string> views)
        {
            if (0 == views.Count) return;
            _views = views;
           
            Thread thread = new Thread(GenerateViewScriptsProc);
            thread.Start();
        }      
       

        public void GenerateDefaultInstallationScript(string groupDirectory, string subDirectory)
        {
            string outDir = Path.Combine(ScriptingLayout.OutputDirectory, groupDirectory);
            outDir = Path.Combine(outDir, subDirectory);
           
            string fileName = Path.Combine(outDir, "RunScripts.bat");
            using (StreamWriter sw = File.CreateText(fileName))
            {
                sw.WriteLine(string.Format("for %%f in (*.sql) do sqlcmd -d {0} -e -i %%f", _database.Name));               
            }
        }

        public void GenerateCreateDatabaseScript()
        {            
            string fileName = Path.Combine(ScriptingLayout.OutputDirectory, "CreateDatabase.sql");
            using (StreamWriter sw = File.CreateText(fileName))
            {
                SqlSmoObject[] objs = new SqlSmoObject[1];

                objs[0] = _database;

                //script drop
                foreach (string str1 in _scripterDrop.Script(objs))
                {
                    sw.WriteLine(str1);
                }

                //script create
                foreach (string str2 in _scripterCreate.Script(objs))
                {
                    sw.WriteLine(str2);
                }
                sw.Close();
            }

            fileName = Path.Combine(ScriptingLayout.OutputDirectory, "CreateDatabase.bat");
            using (StreamWriter sw = File.CreateText(fileName))
            {
                sw.WriteLine("sqlcmd -d Master -e -i CreateDatabase.sql");
                sw.WriteLine("pause");
                sw.WriteLine("InstallSchema.bat");
                sw.WriteLine("InstallViews.bat");
                sw.WriteLine("InstallSprocs.bat");
            }

            string viewsFilename = Path.Combine(ScriptingLayout.OutputDirectory, "InstallViews.bat");
            string sprocsFilename = Path.Combine(ScriptingLayout.OutputDirectory, "InstallSprocs.bat");
            using (StreamWriter swViews = File.CreateText(viewsFilename), swSprocs = File.CreateText(sprocsFilename))
            {                 
                foreach (ScriptingGroup group in _scriptingLayout.Groups)
                {
                    string rootDir = string.Format("\"./{0}", group.GroupName);
                    foreach (string sproc in group.Sprocs)
                    {
                        fileName = string.Format("{0}/{1}/{2}.sql\"", rootDir, _scriptingLayout.SprocsSubDirectory, sproc);
                        swSprocs.WriteLine("sqlcmd -d {0} -e -i {1}", _database.Name, fileName);
                    }
                    foreach (string view in group.Views)
                    {
                        fileName = string.Format("{0}/{1}/{2}.sql\"", rootDir, _scriptingLayout.ViewsSubDirectory, view);
                        swViews.WriteLine("sqlcmd -d {0} -e -i {1}", _database.Name, fileName);
                    }
                }
            }          
        }

        public void GenerateSchemaInstallationScript()        
        {
            string outDir = ScriptingLayout.OutputDirectory;           
           
            // first, get all the tables without relationships...
            List<string> result = GetTopLevelTables();

            // yah - this is kinda goofy, but it works....
            // basically, we first get tables with only 1 relationship,
            // and work our way up the relationship count,
            // and then make 2 passes to make sure we get all tables
            for (int x = 0; x < 2; x++)
            {
                for (int y = 1; y < 6; y++) // more than 5 FK's? oops...
                {
                    BuildTableDependencies(y, ref result);
                }
            }

            string fileName = Path.Combine(outDir, "InstallSchema.bat");
            using (StreamWriter sw = File.CreateText(fileName))
            {
                foreach(string s in result)
                {
                    string pathName = _scriptingLayout.GetTablePathname("./", s);
                    sw.WriteLine(string.Format("sqlcmd -d {0} -e -i \"{1}\"", _database.Name, pathName));
                }
            }           
        }

        private void GenerateSprocScriptsProc()
        {          
            Server server = new Server(_server.Name);

            server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
            server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
            server.SetDefaultInitFields(typeof(View), "IsSystemObject");

            Scripter scripterDrop = BuildDropScripter(server);
            Scripter scripterCreate = BuildCreateScripter(server);
            Database db = server.Databases[_database.Name];

            foreach (StoredProcedure sp in db.StoredProcedures)
            {
                if (!_sprocs.Contains(sp.Name)) continue;            

                SqlSmoObject[] objs = new SqlSmoObject[1];
                objs[0] = sp;

                using (StreamWriter sw = File.CreateText(_scriptingLayout.GetSprocPathname(sp.Name)))
                {
                    //script drop
                    foreach (string str1 in scripterDrop.Script(objs))
                    {
                        WriteLine(sw, str1);
                    }

                    //script create
                    foreach (string str2 in scripterCreate.Script(objs))
                    {
                        WriteLine(sw, str2);
                    }
                    sw.Close();
                }
                OnObjectScripted();
            }
        }

        public void EnsureDirectoriesExist()
        {
            string subDir;
            foreach (ScriptingGroup group in _scriptingLayout.Groups)
            {
                string rootDir = Path.Combine(ScriptingLayout.OutputDirectory, group.GroupName);
                if (!Directory.Exists(rootDir))
                {
                    Directory.CreateDirectory(rootDir);                   
                }

                subDir = Path.Combine(rootDir, _scriptingLayout.SchemaSubDirectory);
                if (!Directory.Exists(subDir))
                {
                    Directory.CreateDirectory(subDir);
                }

                subDir = Path.Combine(rootDir, _scriptingLayout.SprocsSubDirectory);
                if (!Directory.Exists(subDir))
                {
                    Directory.CreateDirectory(subDir);
                }

                subDir = Path.Combine(rootDir, _scriptingLayout.ViewsSubDirectory);
                if (!Directory.Exists(subDir))
                {
                    Directory.CreateDirectory(subDir);
                }
            }

            subDir = Path.Combine(ScriptingLayout.OutputDirectory, _scriptingLayout.SchemaSubDirectory);
            if (!Directory.Exists(subDir))
            {
                Directory.CreateDirectory(subDir);
            }

            subDir = Path.Combine(ScriptingLayout.OutputDirectory, _scriptingLayout.SprocsSubDirectory);
            if (!Directory.Exists(subDir))
            {
                Directory.CreateDirectory(subDir);
            }

            subDir = Path.Combine(ScriptingLayout.OutputDirectory, _scriptingLayout.ViewsSubDirectory);
            if (!Directory.Exists(subDir))
            {
                Directory.CreateDirectory(subDir);
            }
        }

        private void GenerateTableScriptsProc()
        {
            Server server = new Server(_server.Name);

            server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
            server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
            server.SetDefaultInitFields(typeof(View), "IsSystemObject");

            Scripter scripterDrop = BuildDropScripter(server);
            Scripter scripterCreate = BuildCreateScripter(server);
            Database db = server.Databases[_database.Name];

            foreach (Table t in db.Tables)
            {
                if (!_tables.Contains(t.Name)) continue;
                
                SqlSmoObject[] objs = new SqlSmoObject[1];
                objs[0] = t;

                using (StreamWriter sw = File.CreateText(_scriptingLayout.GetTablePathname(ScriptingLayout.OutputDirectory, t.Name)))
                {
                    //script drop
                    foreach (string str1 in scripterDrop.Script(objs))
                    {
                        WriteLine(sw, str1);
                    }

                    //script create
                    foreach (string str2 in scripterCreate.Script(objs))
                    {
                        WriteLine(sw, str2);
                    }
                    sw.Close();
                }
                OnObjectScripted();
            }
        }

        private void GenerateViewScriptsProc()
        {
            Server server = new Server(_server.Name);

            server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
            server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
            server.SetDefaultInitFields(typeof(View), "IsSystemObject");

            Scripter scripterDrop = BuildDropScripter(server);
            Scripter scripterCreate = BuildCreateScripter(server);
            Database db = server.Databases[_database.Name];

            foreach (View v in db.Views)
            {
                if (!_views.Contains(v.Name)) continue;
               
                SqlSmoObject[] objs = new SqlSmoObject[1];
                objs[0] = v;

                using (StreamWriter sw = File.CreateText(_scriptingLayout.GetViewPathname(v.Name)))
                {
                    //script drop
                    foreach (string str1 in scripterDrop.Script(objs))
                    {
                        WriteLine(sw, str1);
                    }

                    //script create
                    foreach (string str2 in scripterCreate.Script(objs))
                    {
                        WriteLine(sw, str2);
                    }
                    sw.Close();
                }
                OnObjectScripted();
            }
        }  

        private List<string> GetTopLevelTables()
        {
            List<string> result = new List<string>();
            foreach (Table table in _database.Tables)
            {
                if (table.IsSystemObject) continue;

                if (0 == table.ForeignKeys.Count)
                {
                    result.Add(table.Name);
                }
            }
            return result;
        }

        private void BuildTableDependencies(int depth, ref List<string> result)
        {     
            foreach (Table table in _database.Tables)
            {
                if (table.IsSystemObject) continue;

                if (depth == table.ForeignKeys.Count)
                {
                    if (result.Contains(table.ForeignKeys[depth-1].ReferencedTable))
                    {
                        if (!result.Contains(table.Name)) result.Add(table.Name);
                        continue;
                    }
                                
                    Table t = _database.Tables[table.ForeignKeys[depth-1].ReferencedTable];
                    if (1 == t.ForeignKeys.Count)
                    {
                        if (!result.Contains(table.Name)) result.Add(table.Name);
                    }                                     
                }
            }            
        }

        private Scripter BuildCreateScripter(Server server)
        {
            //Set Scripting Options
            Scripter scripterCreate = new Scripter(server);

            scripterCreate.Options.AnsiFile = true;
            scripterCreate.Options.AnsiPadding = true;
            scripterCreate.Options.AppendToFile = true;
            scripterCreate.Options.Bindings = true;
            scripterCreate.Options.ClusteredIndexes = true;
            scripterCreate.Options.ContinueScriptingOnError = false;
            scripterCreate.Options.ConvertUserDefinedDataTypesToBaseType = false;
            scripterCreate.Options.DdlBodyOnly = false;
            scripterCreate.Options.DdlHeaderOnly = false;
            scripterCreate.Options.Default = true;
            scripterCreate.Options.DriAll = true;
            scripterCreate.Options.DriAllConstraints = false;
            scripterCreate.Options.DriAllKeys = true;
            scripterCreate.Options.DriChecks = true;
            scripterCreate.Options.DriClustered = true;
            scripterCreate.Options.DriDefaults = true;
            scripterCreate.Options.DriForeignKeys = true;
            scripterCreate.Options.DriIncludeSystemNames = true;
            scripterCreate.Options.DriIndexes = true;
            scripterCreate.Options.DriNonClustered = true;
            scripterCreate.Options.DriPrimaryKey = true;
            scripterCreate.Options.DriUniqueKeys = true;
            scripterCreate.Options.DriWithNoCheck = true;
            scripterCreate.Options.ExtendedProperties = true;
            scripterCreate.Options.FullTextCatalogs = true;
            scripterCreate.Options.FullTextIndexes = true;
            scripterCreate.Options.IncludeHeaders = true;
            scripterCreate.Options.IncludeIfNotExists = true;
            scripterCreate.Options.Indexes = true;
            scripterCreate.Options.NonClusteredIndexes = true;
            scripterCreate.Options.PrimaryObject = true;
            scripterCreate.Options.Triggers = true;
            scripterCreate.Options.WithDependencies = false;
            scripterCreate.Options.XmlIndexes = true;
            scripterCreate.Options.IncludeIfNotExists = false;

            return scripterCreate;
        }

        private Scripter BuildDropScripter(Server server)
        {
            ////scripter for the drop portion of the script
            Scripter scripterDrop = new Scripter(server);

            scripterDrop.Options.AnsiFile = true;
            scripterDrop.Options.AnsiPadding = true;
            scripterDrop.Options.AppendToFile = false;
            scripterDrop.Options.Bindings = true;
            scripterDrop.Options.ClusteredIndexes = true;
            scripterDrop.Options.ContinueScriptingOnError = false;
            scripterDrop.Options.ConvertUserDefinedDataTypesToBaseType = false;
            scripterDrop.Options.DdlBodyOnly = false;
            scripterDrop.Options.DdlHeaderOnly = false;
            scripterDrop.Options.Default = true;
            scripterDrop.Options.DriAll = true;
            scripterDrop.Options.DriAllConstraints = true;
            scripterDrop.Options.DriAllKeys = true;
            scripterDrop.Options.DriChecks = true;
            scripterDrop.Options.DriClustered = true;
            scripterDrop.Options.DriDefaults = true;
            scripterDrop.Options.DriForeignKeys = true;
            scripterDrop.Options.DriIncludeSystemNames = true;
            scripterDrop.Options.DriIndexes = true;
            scripterDrop.Options.DriNonClustered = true;
            scripterDrop.Options.DriPrimaryKey = true;
            scripterDrop.Options.DriUniqueKeys = true;
            scripterDrop.Options.DriWithNoCheck = true;
            scripterDrop.Options.ExtendedProperties = true;
            scripterDrop.Options.FullTextCatalogs = true;
            scripterDrop.Options.FullTextIndexes = true;
            scripterDrop.Options.IncludeDatabaseContext = true;
            scripterDrop.Options.IncludeHeaders = true;
            scripterDrop.Options.IncludeIfNotExists = true;
            scripterDrop.Options.Indexes = true;
            scripterDrop.Options.NonClusteredIndexes = true;
            scripterDrop.Options.PrimaryObject = true;
            scripterDrop.Options.ScriptDrops = true;
            scripterDrop.Options.Triggers = true;
            scripterDrop.Options.WithDependencies = false;
            scripterDrop.Options.XmlIndexes = true;

            return scripterDrop;
        }

        private void WriteLine(StreamWriter sw, string text)
        {
            sw.WriteLine(text);
            sw.WriteLine("GO");
            sw.WriteLine();
        }

        public bool IsTable(string name)
        {
            return _database.Tables.Contains(name);
        }

        public bool IsView(string name)
        {
            return _database.Views.Contains(name);
        }

        public bool IsSproc(string name)
        {
            return _database.StoredProcedures.Contains(name);
        }
    }
}
